package dbLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import modelLayer.Product;

public class DBProducts implements IFDBProducts {

	protected Connection con;

	/**
	 * @param args
	 */
	public DBProducts() {
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public int insertProduct(Product product) throws Exception {
		String query = "INSERT INTO products (carBrand, carModel, engineType, partName, price, stockQuantity) VALUES("
				+ " '"
				+ product.getCarBrand()
				+ "',"
				+ " '"
				+ product.getCarModel()
				+ "',"
				+ " '"
				+ product.getEngineType()
				+ "',"
				+ " '"
				+ product.getPartName()
				+ "',"
				+ " '"
				+ product.getPrice()
				+ "',"
				+ " '"
				+ product.getStockQuantity()
				+ "');";
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Inserting Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Product Inserted!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

	@Override
	public int addProductsToStock(Product product, Product updateProduct) {
		String query = "UPDATE products SET stockQuantity=stockQuantity + "
				+ product.getStockQuantity() + " WHERE productId='"
				+ updateProduct.getProductId() + "'";
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Updating Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Product Updated!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

	@Override
	public Product searchProduct(Product product) {
		String query = "";
		if (product.getProductId() >= 0) {
			query = "SELECT * FROM Products WHERE " + "productId='"
					+ product.getProductId() + "'";
		} else {
			query = "SELECT * FROM Products WHERE ";

			query += "carBrand='" + product.getCarBrand() + "'";

			query += " AND " + "carModel='" + product.getCarModel() + "'";

			query += " AND " + "engineType='" + product.getEngineType() + "'";

			query += " AND " + "partName='" + product.getPartName() + "'";

		}
		ResultSet rs = null;
		LinkedList<Product> data = new LinkedList<Product>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Searching Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				data.addFirst(new Product(rs.getInt("productId"), rs
						.getString("carBrand"), rs.getString("carModel"), rs
						.getString("engineType"), rs.getString("partName"), rs
						.getDouble("price"), rs.getInt("stockQuantity")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		if (data.size() == 1) {
			return data.get(0);
		} else {
			return new Product();
		}
	}

	@Override
	public LinkedList<Product> getAllProducts() throws Exception {
		String query = "SELECT * FROM Products";
		ResultSet rs = null;
		LinkedList<Product> data = new LinkedList<Product>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Searching Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				data.addFirst(new Product(rs.getInt("productId"), rs
						.getString("carBrand"), rs.getString("carModel"), rs
						.getString("engineType"), rs.getString("partName"), rs
						.getDouble("price"), rs.getInt("stockQuantity")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public int updateProduct(Product product, Product updateProduct)
			throws Exception {
		String query = "UPDATE products SET ";
		if (!updateProduct.getCarBrand().equals("")) {
			query += "carBrand='" + updateProduct.getCarBrand() + "' ";
		}
		if (!updateProduct.getCarModel().equals("")) {
			query += ", carModel='" + updateProduct.getCarModel() + "' ";
		}
		if (!updateProduct.getEngineType().equals("")) {
			query += ", engineType='" + updateProduct.getEngineType() + "' ";
		}
		if (!updateProduct.getPartName().equals("")) {
			query += ", partName='" + updateProduct.getPartName() + "' ";
		}
		if (updateProduct.getPrice() > 0) {
			query += ", price='" + updateProduct.getPrice() + "' ";
		}
		if (updateProduct.getStockQuantity() > 0) {
			query += ", stockQuantity=" + updateProduct.getStockQuantity();
		}
		query += " WHERE productId='" + product.getProductId() + "'";
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Updating Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Product Updated!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

	@Override
	public int deleteProduct(Product product) throws Exception {
		String query = "";
		if (product.getProductId() >= 0) {
			query = "DELETE FROM products WHERE productId='"
					+ product.getProductId() + "';";
		} else {
			query = "DELETE FROM products WHERE ";
			if (!product.getCarBrand().equals("")) {
				query += "carBrand='" + product.getCarBrand() + "' ";
			}
			if (!product.getCarModel().equals("")) {
				query += ", carModel='" + product.getCarModel() + "' ";
			}
			if (!product.getEngineType().equals("")) {
				query += ", engineType='" + product.getEngineType() + "' ";
			}
			if (!product.getPartName().equals("")) {
				query += ", partName='" + product.getPartName() + "' ";
			}
			if (product.getPrice() > 0) {
				query += ", price='" + product.getPrice() + "' ";
			}
			if (product.getStockQuantity() > 0) {
				query += ", stockQuantity=" + product.getStockQuantity();
			}
		}
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Updating Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Product Deleted!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

	@Override
	public LinkedList<Product> getAllProducts(Product product) throws Exception {
		String query = "SELECT * FROM Products ";
		if (product.getProductId() >= 0) {
			query = "WHERE productId='"
					+ product.getProductId() + "';";
		} else {
			query += "WHERE ";
			
				query += "carBrand LIKE '%" + product.getCarBrand() + "%' ";
			
			if (!product.getCarModel().equals("")) {
				query += "AND carModel LIKE '%" + product.getCarModel() + "%' ";
			}
			if (!product.getEngineType().equals("")) {
				query += "AND engineType LIKE '%" + product.getEngineType() + "%' ";
			}
			if (!product.getPartName().equals("")) {
				query += "AND partName LIKE '%" + product.getPartName() + "%' ";
			}
		}
		ResultSet rs = null;
		LinkedList<Product> data = new LinkedList<Product>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Searching Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				data.addFirst(new Product(rs.getInt("productId"), rs
						.getString("carBrand"), rs.getString("carModel"), rs
						.getString("engineType"), rs.getString("partName"), rs
						.getDouble("price"), rs.getInt("stockQuantity")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}
}
